package DAO;


import java.sql.*;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import Entity.Funding;
import Entity.Reference;



public class ConDB {
	private ArrayList<Reference> listR;
	Connection con;
	PreparedStatement ps;
	Statement st;
	ResultSet rs;
	boolean addAND=false;

	public ConDB() {
	}

	public void getConnection() {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			con = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1130;database=MockProject;"+ "user=sa;"+ "password=12345678");
		} catch (ClassNotFoundException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(),
					"Driver Not Found", JOptionPane.ERROR_MESSAGE);
			System.exit(1);

		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(),
					"Database Error", JOptionPane.ERROR_MESSAGE);
			System.exit(1);

		}
	}	
	
	public void saveFunding( Funding funding){
		try{
			getConnection();
			String sql= "insert into ServiceFunding values ("
						+"'"+funding.getServiceId()  +"',"
					    +"'"+funding.getFundingSource()  +"',"
						+"'"+funding.getContact()+"',"
					    +"'"+funding.getFundingAmount()+"',"
					    +"'"+funding.getFundingStart()+"',"
					    +"'"+funding.getFundingEnd()+"',"
					    +"'"+funding.getFundingNeeds()+"',"
					    +"'"+funding.getFundingContinuationNeeded()+"',"
					    +"'"+funding.getFundingContinuationAmount()+"',"
					    +"'"+funding.getFundingContinuationDetails() +"',"
					    +"'"+funding.getFundraisingForText()+"',"
					    +"'"+funding.getFundraisingWhy()+"',"
					    +"'"+funding.getFundraisingNeeds()+"')"
					    +"'"+funding.getFundraisingRequiredBy()+"')"
					    +"'"+funding.getFundraisingComplete()+"')"
					    +"'"+funding.getFundraisingCompletedDate()+"')"
					    +"'"+funding.getFundraisingDonorAnonymous()+"')"
					    +"'"+funding.getFundraisingDonorAmount()+"')"
					    +"'"+funding.getFundraisingDonationDate()+"')"
					    +"'"+funding.getFundraisingDonationIncremental()+"')";
			String sql2="insert into ServiceFunding values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement ps = con.prepareStatement(sql2);
			ps.setInt(1, funding.getServiceId());
			ps.setString(2, funding.getFundingSource());
			ps.setString(3, funding.getContact());
			ps.setInt(4, funding.getFundingAmount());
			ps.setDate(5, (Date) funding.getFundingStart());
			ps.setDate(6, (Date) funding.getFundingEnd());
			ps.setString(7, funding.getFundingNeeds());
			ps.setBoolean(8, funding.getFundingContinuationNeeded());
			ps.setInt(9, funding.getFundingContinuationAmount());
			ps.setString(10, funding.getFundingContinuationDetails());
			ps.setString(11, funding.getFundraisingForText());
			ps.setString(12, funding.getFundraisingWhy());
			ps.setString(13, funding.getFundraisingNeeds());
			ps.setDate(14, (Date) funding.getFundraisingRequiredBy());
			ps.setBoolean(15, funding.getFundraisingComplete());
			ps.setDate(16, (Date) funding.getFundraisingCompletedDate());
			ps.setBoolean(17, funding.getFundraisingDonorAnonymous());
			ps.setInt(18, funding.getFundraisingDonorAmount());
			ps.setDate(19, (Date) funding.getFundraisingDonationDate());
			ps.setBoolean(20, funding.getFundraisingDonationIncremental());
			
			ps.executeUpdate();
		}catch(SQLException e){
			JOptionPane.showMessageDialog(null, e.getMessage(),
   					"Database Error", JOptionPane.ERROR_MESSAGE);
		}
		
			finally {
	            if (con != null) {
	               try {
	                  con.close();
	               } catch (Exception e) {
	            	   JOptionPane.showMessageDialog(null, e.getMessage(),
	       					"Database Error", JOptionPane.ERROR_MESSAGE);
	               }
	            }
	         }
		
	}	
	
	
	
	public ArrayList<Funding> getFunding() {
		ArrayList<Funding> listFunding=new ArrayList<Funding>();
		
		try {
			getConnection();
			String sql="SELECT * "+ "FROM ServiceFunding ";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				listFunding.add( new Funding(rs.getInt("FundingID"),rs.getInt("ServiceID"),
						rs.getString("FundingSource"),rs.getString("FundingContactDetails"),
						rs.getInt("FundingAmount"),rs.getDate("FundingStart"),rs.getDate("FundingEnd"),
						rs.getString("FundingNeeds"),rs.getBoolean("FundingContinuationNeeded"),
						rs.getInt("FundingContinuationAmount"),rs.getString("FundingContinuationDetails"),
						rs.getString("FundraisingForText"),rs.getString("FundraisingWhy"),rs.getString("FundraisingNeeds"),
						rs.getDate("FundraisingRequiredBy"),rs.getBoolean("FundraisingComplete"),rs.getDate("FundraisingCompletedDate"),
						rs.getBoolean("FundraisingDonorAnonymous"),rs.getInt("FundraisingDonorAmount"),
						rs.getDate("FundraisingDonationDate"),rs.getBoolean("FundraisingDonationIncremental")
						));
				
			
				
			}
			return listFunding;
		} catch (SQLException e) {
			 JOptionPane.showMessageDialog(null, e.getMessage(),
    					"Database Error", JOptionPane.ERROR_MESSAGE);

		}
		finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
                    e.printStackTrace();
               }
            }
         }
       
                return null;
	}
	
	
	public Funding getFunding(int serviceid){
		Funding funding=null;
		String sql="SELECT * FROM ServiceFunding WHERE ServiceID="+serviceid+"";
		 try{
	        	getConnection();
	        	Statement stmt = con.createStatement();  
	        	ResultSet rs=stmt.executeQuery(sql);
	        	while(rs.next()){	
	        		funding= new Funding(rs.getInt("FundingID"),rs.getInt("ServiceID"),
							rs.getString("FundingSource"),rs.getString("FundingContactDetails"),
							rs.getInt("FundingAmount"),rs.getDate("FundingStart"),rs.getDate("FundingEnd"),
							rs.getString("FundingNeeds"),rs.getBoolean("FundingContinuationNeeded"),
							rs.getInt("FundingContinuationAmount"),rs.getString("FundingContinuationDetails"),
							rs.getString("FundraisingForText"),rs.getString("FundraisingWhy"),rs.getString("FundraisingNeeds"),
							rs.getDate("FundraisingRequiredBy"),rs.getBoolean("FundraisingComplete"),rs.getDate("FundraisingCompletedDate"),
							rs.getBoolean("FundraisingDonorAnonymous"),rs.getInt("FundraisingDonorAmount"),
							rs.getDate("FundraisingDonationDate"),rs.getBoolean("FundraisingDonationIncremental")
							);
	        	}
	        	System.out.println(funding.getContact());
	        }
	        catch(Exception e){
	        	e.printStackTrace();
	        }
	        finally {
	            if (con != null) {
	               try {
	                  con.close();
	               } catch (Exception e) {
	            	   e.printStackTrace();
	               }
	            }
	         }
	            
	    
		return funding;
	}

	
	public ArrayList<Reference> getListReference (String []refCode){
			
			String sql="SELECT RefID,RefCode,RefValue "+
						"FROM ReferenceData " +
						"WHERE ";
			int i;
			for(i=0;i<refCode.length-1;i++){
				sql+=" RefCode= '"+refCode[i]+"' OR ";
			}
			sql+="RefCode= '"+refCode[i]+"'";
	        
	        System.out.println("sql= "+sql);
	        try{
	        	getConnection();
	        	Statement stmt = con.createStatement();  
	        	ResultSet rs=stmt.executeQuery(sql);
	        	int count=0;
	        	listR=new ArrayList<Reference>();
	        	while(rs.next()){
	        		count++;
	        		String refID=rs.getString(1);
	        		String refCodes=rs.getString(2);
	        		String refValue=rs.getString(3);
	        		
	        		System.out.println("ID="+refID+" value="+refValue);
	        		listR.add(new Reference(refID, refCodes, refValue));
	        	}
	        	
	        	return listR;
	        }
	        catch(Exception e){
	        	e.printStackTrace();
	        }
	        finally {
	            if (con != null) {
	               try {
	                  con.close();
	               } catch (Exception e) {
	            	   e.printStackTrace();
	               }
	            }
	         }
	            
	    	return null;
	    }

	public ArrayList<String> getServiceReference (int serviceID){
		String sql="SELECT RefID "+
					"FROM ServiceReference " +
					"WHERE serviceID= "+serviceID;
		
        try{
        	getConnection();
        	Statement stmt = con.createStatement();  
        	ResultSet rs=stmt.executeQuery(sql);
        	
        	ArrayList<String> refID=new ArrayList<String>();
        	while(rs.next()){	
        		String refIDs=rs.getString(1);
        		System.out.println(refIDs);
        		refID.add(refIDs);
        	}
        	return refID;
        }
        catch(Exception e){
        	e.printStackTrace();
        }
        finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
            	   e.printStackTrace();
               }
            }
         }
            
    	return null;
    }
	
	public void deleteReference(int id){
		try{
        	getConnection();
        	Statement stmt = con.createStatement();
        	
        	String sql="";
        	sql="DELETE ServiceReference WHERE serviceID="+id+"";
        	stmt.execute(sql);
        }
        catch(Exception e){
        	e.printStackTrace();
        }
        finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
            	   e.printStackTrace();
               }
            }
         }
    }
		
	public void deleteFunding(int id){
		try{
        	getConnection();
        	Statement stmt = con.createStatement();
        	
        	String sql="";
        	sql="DELETE ServiceFunding WHERE ServiceID="+id+"";
        	stmt.execute(sql);
        }
        catch(Exception e){
        	e.printStackTrace();
        }
        finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
            	   e.printStackTrace();
               }
            }
         }
    }
	
	public  void saveServiceReference(int serviceID, String[] refID){
		
        try{
        	getConnection();
        	Statement stmt = con.createStatement();
        	
        	String sql="";
        	for(int i=0;i<refID.length;i++){
        		sql="INSERT INTO ServiceReference (ServiceID,RefID) VALUES (" +
        				serviceID+",'" +refID[i]+
        				"')";
        		stmt.executeUpdate(sql);
        	}

        }
        catch(Exception e){
        	e.printStackTrace();
        }
        finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
            	   e.printStackTrace();
               }
            }
         }
    }
	
}
